<?php
/**
 * Data Abstraction Layer
 */
 
register_shutdown_function('db_auto_close');
 
/**
 * Connect to DB
 * @param  string  $db_server           Database Server
 * @param  string  $db_user             Database Username
 * @param  string  $db_passwd           Database Password
 * @param  string  $db_name             Database Name
 * @return mysqli                       Connection to DB
 */

function db_connect($db_server, $db_user, $db_passwd, $db_name) {
    $db_conn = @mysqli_connect($db_server, $db_user, $db_passwd, $db_name);
 
    if (mysqli_connect_errno()) {
        $v_message = 'Unable to connect to DB server.';

        if (DEVELOPMENT_ENVIRONMENT) {
            $v_message = $v_message . '<br/>' . mysqli_connect_error();
        }
        
        db_error_output($db_conn, $v_message);
 
        exit;
    }
 
    $db_conn->database = $db_name;
    db_query($db_conn, 'SET NAMES utf8;', $db_name);
 
    $GLOBALS['db_conns'][] = &$db_conn;
 
    return $db_conn;
}
 
/**
 * Close DB connection
 * @param mysqli $db_conn Connection to DB
 */
function db_close(&$db_conn) {
    if($db_conn instanceof mysqli) {
        @mysqli_close($db_conn);
        $db_conn = null;
    }
}
 
/**
 * Auto close all DB connections
 */
function db_auto_close() {
    if(isset($GLOBALS['db_conns']) && is_array($GLOBALS['db_conns'])) {
        foreach($GLOBALS['db_conns'] as &$db_conn) {
            db_close($db_conn);
        }
    }
}
 
/**
 * Query Database
 * @param  mysqli  $db_conn             Database Connection
 * @param  string  $db_query            SQL Query
 * @return mysqli_result                Query Result
 */
function db_query(mysqli &$db_conn, $db_query) {
    $db_result = @mysqli_query($db_conn, $db_query);
 
    if(!$db_result) {
        $v_message = 'Could NOT query.';
        
        if (DEVELOPMENT_ENVIRONMENT) {
            $v_message = $v_message . '<br/>' . db_error($db_conn);
            $v_message = $v_message . '<br/>' . $db_query;
        }
        
        db_error_output($db_conn, $v_message);
 
        exit;
    }
 
    if(is_object($db_result)) {
        // Use for db_fetch_object
        $db_result->database    = $db_conn->database;
        $db_result->classname   = 'stdClass';
 
        // Class name detection
        $v_database     = $db_conn->database;
        $v_tablecount   = 0;
        $v_table        = '';
        $a_fields       = mysqli_fetch_fields($db_result);
 
        foreach($a_fields as $o_field) {
            if($o_field->orgtable) {
                if($v_tablecount) {
                    if($o_field->orgtable != $v_table) {
                        $v_tablecount++;
                        break;
                    }
                }
                else {
                    $v_table = $o_field->orgtable;
                    $v_tablecount++;
                }
            }
        }
 
        if($v_tablecount) {
            if($v_tablecount == 1) {
                $v_classname = class_exists($v_database . '_' . $v_table) ? ($v_database . '_' . $v_table) : (class_exists($v_database) ? $v_database : 'stdClass');
            }
            else {
                $v_classname = class_exists($v_database) ? $v_database : 'stdClass';
            }
 
            $db_result->classname = $v_classname;
        }
    }
 
    return $db_result;
}
 
/**
 * Query Database
 * @param  mysqli  $db_conn             Database Connection
 * @param  string  $db_query            SQL Query
 * @param  int     $s_Page_Start         Start offset
 * @param  int     $i_Page_Size          Limit
 * @return mysqli_result                Query Result
 */
function db_query_page(mysqli &$db_conn, $db_query, $s_Page_Start, $i_Page_Size) {
    $db_query = $db_query . " limit $s_Page_Start, $i_Page_Size";
 
    return db_query($db_conn, $db_query);
}
 
/**
 * Get data row as an object (Alias of db_fetch_object())
 * @param  mysqli_result $db_result  Query  Result
 * @return object                    Data row
 */
function db_fetch(mysqli_result $db_result) {
    return db_fetch_object($db_result);
}
 
/**
 * Get data row as an object
 * @param mysqli_result $db_result  Query  Result
 * @return object                   Data row
 */
function db_fetch_object(mysqli_result $db_result) {
    return mysqli_fetch_object($db_result, $db_result->classname);
}
 
/**
 * Get data row as a object
 * @param mysqli_result $db_result  Query  Result
 * @return stdClass                 Data row
 */
function db_fetch_raw_object(mysqli_result $db_result) {
    return mysqli_fetch_object($db_result);
}
 
/**
 * Get data row as an associative array
 * @param mysqli_result $db_result  Query  Result
 * @return array                    Data row
 */
function db_fetch_array(mysqli_result $db_result) {
    return mysqli_fetch_assoc($db_result);
}
 
/**
 * Get last insert id
 * @param  mysqli $db_conn  Database Connection
 * @return mixed            Last Insert ID
 */
function db_last_insert_id(mysqli $db_conn) {
    return mysqli_insert_id($db_conn);
}
 
/**
 * Gets the number of affected rows in a previous operation
 * @param  mysqli $db_conn  Database Connection
 * @return int              Number of Affected rows
 */
function db_affected_rows(mysqli $db_conn) {
    return mysqli_affected_rows($db_conn);
}
 
/**
 * Gets the number of rows in a result
 * @param  mysqli_result $db_result  Query Result
 * @return int                       Number of Affected rows
 */
function db_num_rows(mysqli_result $db_result) {
    return mysqli_num_rows($db_result);
}
 
/**
 * Returns the error code for the most recent function call
 * @param  mysqli $db_conn  Database Connection
 * @return int              Error code
 */
function db_error(mysqli $db_conn) {
    return mysqli_error($db_conn);
}
 
/**
 * Returns a string description of the last error
 * @param  mysqli $db_conn  Database Connection
 * @return string           Error description
 */
function db_errno(mysqli $db_conn) {
    return mysqli_errno($db_conn);
}
 
/**
 * Escapes a string for use in a query
 * @param  mysqli $db_conn  Database Connection
 * @param  mixed  $v_var    Variable to be escaped
 * @return mixed            Escaped variable
 */
function db_real_escape_string($db_conn, &$v_var) {
    if(!$db_conn) {
        return false;
    }
 
    if (is_array($v_var) == false) {
        return mysqli_real_escape_string($db_conn, $v_var);
    }
 
    foreach ($v_var as $v_key => $v_value) {
        $v_var[$v_key] = db_real_escape_string($db_conn, $v_var[$v_key]);
    }
 
    return $v_var;
}
 
/**
 * Update a row
 * @param  mysqli   $db_conn            Database Connection
 * @param  array    $a_row              Values
 * @param  string   $v_table            Table Name
 * @param  string   $s_Where            WHERE clause
 * @return boolean
 */
function db_update_row(mysqli &$db_conn, array $a_row, $v_table, $s_Where = '') {
    if(empty($a_row) || !is_array($a_row)) {
        return false;
    }
 
    $a_sets = array();
 
    foreach($a_row as $v_column => $v_value) {
        $a_sets[] = "`$v_column` = '$v_value'";
    }
 
    if($s_Where != '') {
        $s_Where = "where $s_Where";
    }
 
    $db_query = implode(', ', $a_sets);
    $db_query = "update `$v_table` set $db_query $s_Where";
 
    return db_query($db_conn, $db_query);
}
 
/**
 * Delete rows
 * @param  mysqli    $db_conn           Database Connection
 * @param  string    $v_table           Table Name
 * @param  string    $s_Where           WHERE clause
 * @return boolean                      Query Result
 */
function db_delete_row(mysqli &$db_conn, $v_table, $s_Where) {
    if($s_Where == '') {
        return false;
    }
 
    $db_query = "delete from `$v_table` where $s_Where";
 
    return db_query($db_conn, $db_query);
}
 
/**
 *
 * @param  mysqli   $db_conn            Database Connection
 * @param  array    $a_row              Data Row or Rows
 * @param  string   $v_table            Table Name
 * @return boolean
 */
function db_insert_row(mysqli &$db_conn, array $a_row, $v_table) {
    if(empty($a_row) || !is_array($a_row)) {
        return false;
    }
 
    $a_array  = array_filter($a_row, 'is_array');
    $v_count  = count($a_array);
    $db_query = '';
    $db_value = '';
 
    if($v_count){
        $v_success = 0;
 
        foreach($a_array as $v_row) {
            $v_result = db_insert_row($db_conn, $v_row, $v_table);
 
            if($v_result !== false) {
                $v_success++;
            }
        }
 
        return $v_success;
    }
 
    $a_columns = array_keys($a_row);
    $a_values  = array_values($a_row);
 
    $db_query = '`' . implode('`, `', $a_columns) . '`';
    $db_value = "'" . implode("', '", $a_values ) . "'";
 
    $db_query = "insert into `$v_table` ($db_query) values ($db_value)";
 
    return db_query($db_conn, $db_query);
}
 
/**
 *
 * @param  mysqli   $db_conn            Database Connection
 * @param  array    $a_row              Data Row or Rows
 * @param  array    $a_row_duplicate    Data Row or Rows Duplicate
 * @param  string   $v_table            Table Name
 * @return boolean
 */
function db_insert_duplicate_row(mysqli &$db_conn, array $a_row, array $a_row_duplicate, $v_table) {
    if(empty($a_row) || !is_array($a_row) || empty($a_row_duplicate) || !is_array($a_row_duplicate)) {
        return false;
    }
 
    $a_array  = array_filter($a_row, 'is_array');
    $v_count  = count($a_array);
    $db_query = '';
    $db_value = '';
 
    if($v_count){
        $v_success = 0;
 
        foreach($a_array as $v_row) {
            $v_result = db_insert_duplicate_row($db_conn, $v_row, $a_row_duplicate, $v_table);
 
            if($v_result !== false) {
                $v_success++;
            }
        }
 
        return $v_success;
    }
 
    $a_columns          = array_keys($a_row);
    $a_values           = array_values($a_row);

    foreach ($a_row_duplicate as $key => $value) { $a_duplicate_values[] = "`$key`='$value'"; }
 
    $db_query           = '`' . implode('`, `', $a_columns) . '`';
    $db_value           = '"' . implode('", "', $a_values) . '"';
    $db_duplicate_value = '' . implode(', ', $a_duplicate_values) . '';
    
    $db_query = "insert into `$v_table` ($db_query) values ($db_value) on duplicate key update " . $db_duplicate_value;

    return db_query($db_conn, $db_query);
}
 
/**
 * Build SELECT ... FROM ... clause for query string
 * @param  array    $a_tablecolumns     Table Columns
 * @return string                       SELECT clause of query
 */
function db_get_query($a_tablecolumns) {
    $db_query = 'select `' . implode('`, `', $a_tablecolumns['columns']) . '` from `' . $a_tablecolumns['name'] . '` ';
    return $db_query;
}
 
/**
 * Get Single Value from query
 * @param  mysqli   $db_conn            Database Connection
 * @param  string   $db_query           Database Query
 * @return string                       Returned Value
 */
function db_query_single_value(mysqli &$db_conn, $db_query) {
    $v_result  = null;
    $db_result = db_query($db_conn, $db_query);
 
    if(is_object($db_result) && db_num_rows($db_result)) {
        $d_result = db_fetch_array($db_result);
        $v_result = array_shift($d_result);
    }
 
    return $v_result;
}
 
/**
 * Send DB error email to Technical Staff
 * @param  string   $v_message  Error message
 */
function db_error_output(&$db_conn, &$v_message) {
    db_close($db_conn);
    db_send_error_email();
    include 'lib_error_db_page.php';
}
 
/**
 * Send DB error email to Technical Staff
 */
function db_send_error_email() {
    return true;
}